package org.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.Calendar;
import java.util.Collection;
import com.briup.smart.env.entity.Environment;
import com.briup.smart.env.server.DBStore;
import org.smart.JDBC;

public class DBStoreImpl implements DBStore {

/*
    @Override
    public void saveDB(Collection<Environment> collection) {
        // 遍历数据并根据日期创建PreparedStatement以及SQL语句
        // 使用批处理进行数据插入
        // 数据库连接四要素
        // 驱动
        String driverClass = "com.mysql.cj.jdbc.Driver";
        // 数据库地址
        String url = "jdbc:mysql://127.0.0.1:3306/smart";
        // 数据库的用户名
        String username = "root"; // 用户名
        // 数据库的用户密码
        String password = "1234"; // 密码
        // jdbc六大步
        // 连接对象
        Connection conn = null;
        // pStmt
        PreparedStatement pStmt = null;
        // 准备计数器 进行少量数据的批处理、事务提交
        int count = 0;
        // 成功入库的条数
        int saveCount = 0;
        try {
            // 1. 加载注册驱动
            Class.forName(driverClass);
            // 2. 获取连接对象
            conn = DriverManager.getConnection(url, username, password);
            // 设置事务手动提交
            conn.setAutoCommit(false);
            // 3. 获取 PreparedStatement 对象
            // 3.1 准备sql
            String sql = "INSERT INTO e_detail_1 (name,srcId,desId,devId,sersorAddress,count,cmd,status,data,gather_date) VALUES(?,?,?,?,?,?,?,?,?,?)";
            // System.out.println(sql);
            // 3.2 预编译sql
            pStmt = conn.prepareStatement(sql);
            // 3.3 执行sql
            for (Environment smart : collection) {
                pStmt.setString(1, smart.getName());
                pStmt.setString(2, smart.getSrcId());
                pStmt.setString(3, smart.getDesId());
                pStmt.setString(4, smart.getDevId());
                pStmt.setString(5, smart.getSensorAddress());
                pStmt.setInt(6, smart.getCount());
                pStmt.setString(7, smart.getCmd());
                pStmt.setInt(8, smart.getStatus());
                pStmt.setFloat(9, smart.getData());
                pStmt.setTimestamp(10, smart.getGatherDate());
                count++;
                // 添加到 批处理
                pStmt.addBatch();
                // 4.3 执行插入操作
                if (count % 3 == 0) {
                    pStmt.executeBatch();
                    conn.commit();
                    // 记录实际入库数据
                    saveCount += count;
                    // 重置计数器为0
                    count = 0;
                }
            }
            // 3.4 出循环再次执行批处理
            pStmt.executeBatch();
            // 提交事务
            conn.commit();
            // 记录实际入库数据
            saveCount += count;
            System.out.println("服务端：入库模块，成功入库" + saveCount + "条数据");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            if (pStmt != null) {
                try {
                    pStmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            System.out.println("服务端：入库模块，成功关闭");
        }
    }*/


    private JDBC jdbc = new JDBC();

    @Override
    public void saveDB(Collection<Environment> collection) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        //实际入库条数
        int saveCount = 0;
        try {
            //1 2.获取数据库连接
            conn = jdbc.getConnection();
            //System.out.println("conn: " + conn);
            conn.setAutoCommit(false);
            // 准备计数器
            int count = 0;
            //
            int preDay = -1;
            int currDay = -1;
            for(Environment env : collection) {
                //获取采集天
                Timestamp gatherDate = env.getGatherDate();
                Calendar cal = Calendar.getInstance();
                cal.setTimeInMillis(gatherDate.getTime());
                currDay = cal.get(Calendar.DAY_OF_MONTH);
                if(preDay == -1 || preDay != currDay) {
                    //如果读取到新的一天，则将前一个pstmt对象
                    if(preDay != -1) {
                        pstmt.executeBatch();
                        conn.commit();
                        saveCount += count;
                        count = 0;
                        pstmt.close();
                        System.out.println("提交事务,saveCount: "
                                + saveCount);
                    }
                    //3.获取pstmt对象
                    String sql = "insert into e_detail_"+currDay+" (name,srcId,desId,devId,sensorAddress,count,cmd,status,data,gather_date) " + "values(?,?,?,?,?,?,?,?,?,?)";
                    pstmt = conn.prepareStatement(sql);
                    System.out.println("创建新pstmt: " + sql);
                }
                //4.1 设置?值
                pstmt.setString(1, env.getName());
                pstmt.setString(2, env.getSrcId());
                pstmt.setString(3, env.getDesId());
                pstmt.setString(4, env.getDevId());
                pstmt.setString(5, env.getSensorAddress());
                pstmt.setInt(6, env.getCount());
                pstmt.setString(7, env.getCmd());
                pstmt.setInt(8, env.getStatus());
                pstmt.setFloat(9, env.getData());
                pstmt.setTimestamp(10, env.getGatherDate());
                count++;
                //4.2 添加到批处理
                pstmt.addBatch();
                //4.3 执行插入操作
                if(count % 3 == 0) {
                    pstmt.executeBatch();
                    //提交事务
                    conn.commit();
                    //记录实际入库数据
                    saveCount += count;
                    //重置计数器为0
                    count = 0;
                    System.out.println("提交事务,saveCount: " +
                            saveCount);
                }
                //当前数据处理完成，记录当前天数
                preDay = currDay;
            }
            //4.4 出循环再次执行批处理
            pstmt.executeBatch();
            //提交事务
            conn.commit();
            //记录实际入库数据
            saveCount += count;
            System.out.println("成功入库数据条数：" + saveCount);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            // 关闭资源
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}
